package br.com.arndroid.coupman.model;

import java.text.ParseException;
import java.text.SimpleDateFormat;

import br.com.arndroid.coupman.site.SitePOJO;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;

public class CouponDAO {

	/* This class implements a singleton pattern
	 * For resources saving, the instance must be obtained from getInstance(Context).
	 */
	
	// State constants:
	public static final int STATE_APPROVAL = 1;
	public static final int STATE_AVAILABLE = 2;
	public static final int STATE_USED = 3;
	public static final int STATE_LOST = 4;
	// View constants:
	public static final int VIEW_ALL = 1;
	public static final int VIEW_CATEGORY = 2;
	public static final int VIEW_DUE_DATE = 3;
	public static final int VIEW_SCHEDULE = 4;
	public static final int VIEW_SITE = 5;
	public static final int VIEW_LOCAL = 6;
	public static final int VIEW_STATE = 7;
	// Internal constants:
	private static int DB_ERROR_CODE = -1;
	private static int DB_NO_ACTION = 0;
	
	private static CouponDAO globalInstance = new CouponDAO();
	
	private SQLiteDatabase db;
	
	private static boolean isInMemory = false;
	
	private static boolean isInMemoryChanged = false;
	
	private SimpleDateFormat dateDBFormatter = new SimpleDateFormat("yyyy-MM-dd");
	
	protected CouponDAO() {	}

	public static synchronized CouponDAO getInstance(Context ctx) {
		// Check for database initialization and openess:
		if(globalInstance.db == null || !globalInstance.db.isOpen() || isInMemoryChanged) {
			if(globalInstance.db != null && globalInstance.db.isOpen()) 
				globalInstance.db.close();				
			globalInstance.db = new CouponOpenHelper(ctx, isInMemory).getWritableDatabase();
			isInMemoryChanged = false;
		}
		return globalInstance;
	}

	public static synchronized void closeInstance() {
		if(globalInstance.db != null && globalInstance.db.isOpen()) 
			globalInstance.db.close();
	}
	
	// DB factory methods hock. Use this for tests purposes:
	public static synchronized void setIsInMemory(boolean value) {
		if(isInMemory != value) {
			isInMemory = value;
			isInMemoryChanged = true;
		}
	}
	
	// Category Methods
	public boolean isCategoryInDB(long id) {
		boolean result;
		Cursor cur = db.query(CouponOpenHelper.CATEGORY_TABLE_NAME, new String[] {CouponOpenHelper.CATEGORY_TABLE_ID_COL_NAME}, CouponOpenHelper.CATEGORY_TABLE_ID_COL_NAME + " = ?", new String[] {String.valueOf(id)}, null, null, null);
		try {
			result = cur.getCount() > 0;
		} finally {
			cur.close();
		}
		return result;
	}
	
	public CategoryPOJO getCategoryById(long id) {
		Cursor cursor;
		CategoryPOJO result;

		// Execute query:
		cursor = db.query(CouponOpenHelper.CATEGORY_TABLE_NAME, null, CouponOpenHelper.CATEGORY_TABLE_ID_COL_NAME + " = ?", new String[] {String.valueOf(id)}, null, null, null);
		try {
			if(cursor.moveToFirst()) {
				result = new CategoryPOJO();
				fillCategoryWithCursor(cursor, result);
			} else {
				throw new IllegalArgumentException("id must point to a valid record.");
			}
			return result;
		} finally {
			cursor.close();
		}
	}

	public Cursor getCategories() {
		return db.query(CouponOpenHelper.CATEGORY_TABLE_NAME, null, null, null, null, null, CouponOpenHelper.CATEGORY_TABLE_NAME_COL_NAME);
	}

	public long saveCategory(CategoryPOJO category) {
		// If category have a id then update, otherwise insert a new row.
		// The return is the id for newly or updated record.
		// If update or insert may not be executed (e.g. constraints violations) a exception is throw
		// (inside insert/updateCategory()).
		
		return category._id == null ? insertCategory(category) : updateCategory(category); 
	}
	
	private long insertCategory(CategoryPOJO category) {
		long resultId = db.insert(CouponOpenHelper.CATEGORY_TABLE_NAME, null, fillContentWithCategoryNoIdAttrs(category));
		if(resultId == DB_ERROR_CODE)
			throw new IllegalArgumentException("Insert operation may not be executed with informed data");
		return resultId;
	}
	
	private long updateCategory(CategoryPOJO category) {
		int affectedRows = db.update(CouponOpenHelper.CATEGORY_TABLE_NAME, fillContentWithCategoryNoIdAttrs(category), CouponOpenHelper.CATEGORY_TABLE_ID_COL_NAME + " = ?", new String[] {String.valueOf(category._id)}); 
		if(affectedRows == 1)
			return category._id;
		else
			throw new IllegalArgumentException("Inconsistent update operation: zero or more then one rows affected");
	}	
	
	private ContentValues fillContentWithCategoryNoIdAttrs(CategoryPOJO category) {
		ContentValues cv = new ContentValues();
		cv.put(CouponOpenHelper.CATEGORY_TABLE_NAME_COL_NAME, category.name);
		cv.put(CouponOpenHelper.CATEGORY_TABLE_ICON_URI_COL_NAME, category.icon_uri);
		return cv;
	}
	
	public void removeCategory(long id) {
		int result = 0;
		
		db.beginTransaction();
		try {
			// Clean-up childs:
			clearCouponsCategory(id);
			// Delete parent:
			result = db.delete(CouponOpenHelper.CATEGORY_TABLE_NAME, CouponOpenHelper.CATEGORY_TABLE_ID_COL_NAME + " = ?", new String[] {String.valueOf(id)});
			if(result == DB_NO_ACTION)
				throw new IllegalArgumentException("Informed id not found.");
			// Commit:
			db.setTransactionSuccessful();
		} finally {			
			db.endTransaction();
		}
	}
	
	public void fillCategoryWithCursor(Cursor cursor, CategoryPOJO buffer) {
		buffer._id = cursor.getLong(CouponOpenHelper.CATEGORY_TABLE_ID_COL_INDEX);
		buffer.name = cursor.getString(CouponOpenHelper.CATEGORY_TABLE_NAME_COL_INDEX);
		buffer.icon_uri = cursor.getString(CouponOpenHelper.CATEGORY_TABLE_ICON_URI_COL_INDEX);
	}
	
	// Site methods:	
	public boolean isSiteInDB(long id) {
		boolean result;
		Cursor cur = db.query(CouponOpenHelper.SITE_TABLE_NAME, new String[] {CouponOpenHelper.SITE_TABLE_ID_COL_NAME}, CouponOpenHelper.SITE_TABLE_ID_COL_NAME + " = ?", new String[] {String.valueOf(id)}, null, null, null);
		try {
			result = cur.getCount() > 0;
		} finally {
			// TODO Auto-generated catch block
			cur.close();
		}
		return result;
	}
	
	public Long getSiteIdByName(String name) {
		Cursor cursor;
		Long result;
		String nameToFind = name == null ? new String("") : name;

		// Execute query:
		cursor = db.query(CouponOpenHelper.SITE_TABLE_NAME, null, CouponOpenHelper.SITE_TABLE_NAME_COL_NAME + " = ?", new String[] {nameToFind}, null, null, null);
		
		try {
			if(cursor.moveToFirst()) {
				result = cursor.getLong(CouponOpenHelper.SITE_TABLE_ID_COL_INDEX); 
			} else {
				result = null;
			}
		} finally {
			cursor.close();
		}
		return result;
	}
	
	public SitePOJO getSiteById(long id) {
		Cursor cursor;
		SitePOJO result;
		// Execute query:
		cursor = db.query(CouponOpenHelper.SITE_TABLE_NAME, null, CouponOpenHelper.SITE_TABLE_ID_COL_NAME + " = ?", new String[] {String.valueOf(id)}, null, null, null);
		try {
			if(cursor.moveToFirst()) {
				result = new SitePOJO();
				fillSiteWithCursor(cursor, result);
			} else {
				throw new IllegalArgumentException("id must point to a valid record.");
			}
			return result;
		} finally {
			cursor.close();
		}
	}

	public Cursor getSites() {
		return db.query(CouponOpenHelper.SITE_TABLE_NAME, null, null, null, null, null, CouponOpenHelper.SITE_TABLE_NAME_COL_NAME);
	}

	public long saveSite(SitePOJO site) {
		// If site have a id then update, otherwise insert a new row.
		// The return the id for newly or updated record.
		// If update or insert may not be executed (e.g. constraints violations) a exception is throw
		// (inside insert/updateSite()).
		return site._id == null ? insertSite(site) : updateSite(site); 
	}

	private long insertSite(SitePOJO site) {
		long resultId = db.insert(CouponOpenHelper.SITE_TABLE_NAME, null, fillContentWithSiteNoIdAttrs(site));
		if(resultId == DB_ERROR_CODE)
			throw new IllegalArgumentException("Insert operation may not be executed with informed data");
		return resultId;
	}
	
	private long updateSite(SitePOJO site) {
		int affectedRows = db.update(CouponOpenHelper.SITE_TABLE_NAME, fillContentWithSiteNoIdAttrs(site), CouponOpenHelper.SITE_TABLE_ID_COL_NAME + " = ?", new String[] {String.valueOf(site._id)});
		if(affectedRows == 1)
			return site._id;
		else
			throw new IllegalArgumentException("Inconsistent update operation: zero or more then one rows affected");
	}	
	
	private ContentValues fillContentWithSiteNoIdAttrs(SitePOJO site) {
		ContentValues cv = new ContentValues();
		cv.put(CouponOpenHelper.SITE_TABLE_NAME_COL_NAME, site.name);
		cv.put(CouponOpenHelper.SITE_TABLE_URL_URI_COL_NAME, site.url_uri);
		return cv;
	}
	
	public void removeSite(long id) {
		int result = 0;
		
		db.beginTransaction();
		try {
			// Clean-up childs:
			clearCouponsSite(id);
			// Delete parent:
			result = db.delete(CouponOpenHelper.SITE_TABLE_NAME, CouponOpenHelper.SITE_TABLE_ID_COL_NAME + " = ?", new String[] {String.valueOf(id)});
			if(result == DB_NO_ACTION)
				throw new IllegalArgumentException("Informed id not found.");
			// Commit:
			db.setTransactionSuccessful();
		} finally {			
			db.endTransaction();
		}
	}

	public void fillSiteWithCursor(Cursor cursor, SitePOJO buffer) {
		buffer._id = cursor.getLong(CouponOpenHelper.SITE_TABLE_ID_COL_INDEX);
		buffer.name = cursor.getString(CouponOpenHelper.SITE_TABLE_NAME_COL_INDEX);
		buffer.url_uri = cursor.getString(CouponOpenHelper.SITE_TABLE_URL_URI_COL_INDEX);
	}	
	
	// Coupon Methods:
	public CouponPOJO getCouponById(long id) {
		Cursor cursor;
		CouponPOJO result = null;
		// Execute query:
		// TODO: Change to query method?
		cursor = db.rawQuery("SELECT * FROM coupon WHERE _id = " + id, null);
		try {
			if(cursor.moveToFirst()) {
				// Fill the VO
				result = new CouponPOJO();
				// Id:
				result._id = cursor.getLong(CouponOpenHelper.COUPON_TABLE_ID_COL_INDEX);
				// Summary:
				result.summary = cursor.getString(CouponOpenHelper.COUPON_TABLE_SUMMARY_COL_INDEX);
				// Notes:
				result.notes = cursor.isNull(CouponOpenHelper.COUPON_TABLE_NOTES_COL_INDEX)? null : cursor.getString(CouponOpenHelper.COUPON_TABLE_NOTES_COL_INDEX);
				// Normal value:
				result.normal_value = cursor.isNull(CouponOpenHelper.COUPON_TABLE_NORMAL_VALUE_COL_INDEX)? null : cursor.getFloat(CouponOpenHelper.COUPON_TABLE_NORMAL_VALUE_COL_INDEX);
				// Promo value:
				result.promo_value = cursor.isNull(CouponOpenHelper.COUPON_TABLE_PROMO_VALUE_COL_INDEX)? null : cursor.getFloat(CouponOpenHelper.COUPON_TABLE_PROMO_VALUE_COL_INDEX);
				// Expiration date:
				if(!cursor.isNull(CouponOpenHelper.COUPON_TABLE_EXPIRATION_DT_COL_INDEX)) {
					try {
						result.expiration_dt = dateDBFormatter.parse(cursor.getString(CouponOpenHelper.COUPON_TABLE_EXPIRATION_DT_COL_INDEX));
					} catch (ParseException e) {
						throw new SQLiteException(e.getMessage());
					}	
				} else
					result.expiration_dt = null;
				// Reservation required:
				result.reservation_required = cursor.isNull(CouponOpenHelper.COUPON_TABLE_RESERVATION_REQUIRED_COL_INDEX)? null : cursor.getInt(CouponOpenHelper.COUPON_TABLE_RESERVATION_REQUIRED_COL_INDEX) == 1;
				// Reservation date:
				if(!cursor.isNull(CouponOpenHelper.COUPON_TABLE_SCHEDULE_DT_COL_INDEX)) {
					try {
						result.schedule_dt = dateDBFormatter.parse(cursor.getString(CouponOpenHelper.COUPON_TABLE_SCHEDULE_DT_COL_INDEX));
					} catch (ParseException e) {
						throw new SQLiteException(e.getMessage());
					}
				} else
					result.schedule_dt = null;
				// Local:
				result.local = cursor.isNull(CouponOpenHelper.COUPON_TABLE_LOCAL_COL_INDEX)? null : cursor.getString(CouponOpenHelper.COUPON_TABLE_LOCAL_COL_INDEX);
				// URI media:
				result.uri_media = cursor.isNull(CouponOpenHelper.COUPON_TABLE_URI_MEDIA_COL_INDEX)? null : cursor.getString(CouponOpenHelper.COUPON_TABLE_URI_MEDIA_COL_INDEX);
				// Category:
				result.category = cursor.isNull(CouponOpenHelper.COUPON_TABLE_FK_CATEGORY_COL_INDEX)? null : getCategoryById(cursor.getInt(CouponOpenHelper.COUPON_TABLE_FK_CATEGORY_COL_INDEX));
				// Site:
				result.site = cursor.isNull(CouponOpenHelper.COUPON_TABLE_FK_SITE_COL_INDEX)? null : getSiteById(cursor.getInt(CouponOpenHelper.COUPON_TABLE_FK_SITE_COL_INDEX));
				// State:
				result.state = cursor.getInt(CouponOpenHelper.COUPON_TABLE_STATE_COL_INDEX);
			}
		} finally {
			cursor.close();
		}
		
		return result;
	}
	
	private int clearCouponsCategory(long idCategory) {
		int result;
		ContentValues cv = new ContentValues();
		
		cv.putNull(CouponOpenHelper.COUPON_TABLE_FK_CATEGORY_COL_NAME);
		result = db.update(CouponOpenHelper.COUPON_TABLE_NAME, cv, CouponOpenHelper.COUPON_TABLE_FK_CATEGORY_COL_NAME + " = ?", new String[] {String.valueOf(idCategory)});
		return result;
	}

	private int clearCouponsSite(long idSite) {
		int result;
		ContentValues cv = new ContentValues();
		
		cv.putNull(CouponOpenHelper.COUPON_TABLE_FK_SITE_COL_NAME);
		result = db.update(CouponOpenHelper.COUPON_TABLE_NAME, cv, CouponOpenHelper.COUPON_TABLE_FK_SITE_COL_NAME + " = ?", new String[] {String.valueOf(idSite)});
		return result;
	}

	public Cursor getCouponHeaders(int vision, boolean includeUseds, boolean includeLosts) {
		// TODO Auto-generated method stub
		return null;
	}

	public long saveCoupon(CouponPOJO coupon) {
		// TODO Auto-generated method stub
		return 0;
	}

	public void removeCoupon(long id) {
		// TODO Auto-generated method stub
	}
	
	public boolean isCouponInDB(long id) {
		// TODO Auto-generated method stub
		return false;
	}

	public void fillCouponWithCursor(Cursor cursor, CouponPOJO buffer) {
		// TODO Auto-generated method stub
		
	}
	
	protected void finalize() throws Throwable	{
	  if(db.isOpen())
		  db.close();
	  super.finalize();
	} 
}